package com.hethongviet.vending.helper.db;

import java.util.ArrayList;
import java.util.List;

import com.hethongviet.vending.Utils;
import com.hethongviet.vending.model.Account;

import android.content.Context;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;

public class AccountSQLite
{
	public static final String TABLE_NAME = "accounts";

	public static final String COLUMN_ID_DB = "idDB";

	public static final String COLUMN_NAME = "name";
	public static final String COLUMN_AMOUNT = "AccountAmount";
	public static final String COLUMN_PERMIT_ACCESS = "permit_access";

	public static final String COLUMN_IS_TERMINATED = "is_terminated";
	public static final String COLUMN_IS_ACTIVE = "active";
	public static final String COLUMN_ID = "AccountID";
	public static final String COLUMN_DATE_CREATED = "DateCreate";

	public static final String COLUMN_GROUP_ID = "AccountGroupID";
	public static final String COLUMN_PIN_CODE = "PinCode";
	public static final String COLUMN_STAFF_ID = "StaffID";
	public static final String COLUMN_USER_NAME = "Username";

	public static final String COLUMN_PASSWORD = "Pass";
	public static final String COLUMN_POSITION_ID = "PositionID";

	// Database fields
	private SQLiteDatabase _database;
	private MySQLiteHelper _dbHelper;
	private String[] _allColumns = { COLUMN_ID_DB,

	COLUMN_AMOUNT, COLUMN_DATE_CREATED, COLUMN_GROUP_ID,

	COLUMN_ID, COLUMN_IS_ACTIVE, COLUMN_IS_TERMINATED, COLUMN_NAME,

	COLUMN_PASSWORD, COLUMN_PERMIT_ACCESS, COLUMN_PIN_CODE, COLUMN_POSITION_ID,

	COLUMN_STAFF_ID, COLUMN_USER_NAME };

	public static final String STRING_COLUMNS = COLUMN_ID_DB
			+ " INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL UNIQUE, "

			+ COLUMN_AMOUNT + " integer, "
			+ COLUMN_DATE_CREATED + " integer, " + COLUMN_GROUP_ID
			+ " integer, "

			+ COLUMN_ID + " text, " + COLUMN_IS_ACTIVE + " integer, "
			+ COLUMN_IS_TERMINATED + " integer, " + COLUMN_NAME + " text, "

			+ COLUMN_PASSWORD + " text, " + COLUMN_PERMIT_ACCESS + " integer, "
			+ COLUMN_PIN_CODE + " text, " + COLUMN_POSITION_ID + " integer, "

			+ COLUMN_STAFF_ID + " text, " + COLUMN_USER_NAME + " text";

	public AccountSQLite(Context context)
	{
		List<String> tableNames = new ArrayList<String>();
		List<String> tableColumns = new ArrayList<String>();

		tableNames.add(TABLE_NAME);
		tableNames.add(ProductSQLite.TABLE_NAME);
		tableNames.add(BoxSQLite.TABLE_NAME);
		tableNames.add(MachineSQLite.TABLE_NAME);
		tableNames.add(ProductSellSQLite.TABLE_NAME);
		tableNames.add(ProductServiceSQLite.TABLE_NAME);
		tableNames.add(BoxInventorySQLite.TABLE_NAME);

		tableColumns.add(STRING_COLUMNS);
		tableColumns.add(ProductSQLite.STRING_COLUMNS);
		tableColumns.add(BoxSQLite.STRING_COLUMNS);
		tableColumns.add(MachineSQLite.STRING_COLUMNS);
		tableColumns.add(ProductSellSQLite.STRING_COLUMNS);
		tableColumns.add(ProductServiceSQLite.STRING_COLUMNS);
		tableColumns.add(BoxInventorySQLite.STRING_COLUMNS);

		_dbHelper = new MySQLiteHelper(context, tableNames, tableColumns);
	}

	public void open() throws SQLException
	{
		_database = _dbHelper.getWritableDatabase();
	}

	public void close()
	{
		_dbHelper.close();
	}

	public Account getById(String id)
	{
		open();
		Account account = null;

		Cursor cursor = _database.rawQuery("SELECT * FROM " + TABLE_NAME
				+ " WHERE " + COLUMN_ID + " = ?", new String[] { id });
		cursor.moveToFirst();

		while (!cursor.isAfterLast())
		{

			account = cursorToAccount(cursor);
			break;
		}

		cursor.close();
		close();
		return account;
	}

	private boolean dataExist(Account account, Cursor cursor)
	{
		if (cursor == null)
		{
			return false;
		}
		for (cursor.moveToFirst(); !cursor.isAfterLast(); cursor.moveToNext())
		{
			long idDB = cursor.getLong(cursor.getColumnIndex(COLUMN_ID_DB));
			if (idDB == account.getIdDB()) return true;
		}
		return false;
	}

	/**
	 * @param account
	 * @return row id -1: insert fail
	 */
	public long insert(Account account)
	{
		open();

		Utils.debug("insert --account: " + account.getId());
		Cursor cursor = _database.rawQuery("SELECT * FROM " + TABLE_NAME
				+ " WHERE " + COLUMN_ID_DB + " = " + account.getIdDB(), null);

		long temp = -1;
		if (!dataExist(account, cursor))
		{
			Utils.debug("insert 1111111111111 INSERT");
			/* Insert to database */
			/* Insert new */
			Cursor cursorInsert = _database.rawQuery("INSERT INTO "
					+ TABLE_NAME + "("

					+ COLUMN_AMOUNT + ", "
					+ COLUMN_DATE_CREATED + ", " + COLUMN_GROUP_ID + ", "

					+ COLUMN_ID + ", " + COLUMN_IS_ACTIVE + ", "
					+ COLUMN_IS_TERMINATED + ", " + COLUMN_NAME + ", "

					+ COLUMN_PASSWORD + ", " + COLUMN_PERMIT_ACCESS + ", "
					+ COLUMN_PIN_CODE + ", " + COLUMN_POSITION_ID + ", "

					+ COLUMN_STAFF_ID + ", " + COLUMN_USER_NAME + ") "
					+ "values(?, ?, ?, " + "?, ?, ?, ?, " + "?, ?, ?, ?, "
					+ "?, ?)",

			new String[] { account.getAmount() + "", account.getDateCreated()
					+ "", account.getGroupId() + "",

			account.getId(), "" + (account.isActive() ? 1 : 0), ""
					+ (account.isTerminated() ? 1 : 0), account.getName(),

			account.getPassword(), account.getPermitAccess() + "", account.getPinCode(), account.getPositionId()
					+ "",

			account.getStaffId(), account.getUsername() });

			cursorInsert.moveToFirst();
			if (!cursorInsert.isAfterLast()) temp = cursorInsert.getLong(cursorInsert.getColumnIndex(COLUMN_ID_DB));

			cursorInsert.close();
		}
		else
		{
			Utils.debug("insert 1111111111111 update");
			/* Update to database */
			Cursor cursorUpdate = _database.rawQuery("UPDATE " + TABLE_NAME
					+ " SET "

					+ COLUMN_AMOUNT + " = ?, "
					+ COLUMN_DATE_CREATED + " = ?, " + COLUMN_GROUP_ID
					+ " = ?, "

					+ COLUMN_ID + " = ?, " + COLUMN_IS_ACTIVE + " = ?, "
					+ COLUMN_IS_TERMINATED + " = ?, " + COLUMN_NAME + " = ?, "

					+ COLUMN_PASSWORD + " = ?, " + COLUMN_PERMIT_ACCESS
					+ " = ?, " + COLUMN_PIN_CODE + " = ?, "
					+ COLUMN_POSITION_ID + " = ?, "

					+ COLUMN_STAFF_ID + " = ?, " + COLUMN_USER_NAME

					+ " = ? WHERE " + COLUMN_ID_DB + " = " + account.getIdDB(),

			new String[] { account.getAmount() + "", account.getDateCreated()
					+ "", account.getGroupId() + "",

			account.getId(), "" + (account.isActive() ? 1 : 0), ""
					+ (account.isTerminated() ? 1 : 0), account.getName(),

			account.getPassword(), account.getPermitAccess() + "", account.getPinCode(), account.getPositionId()
					+ "",

			account.getStaffId(), account.getUsername() });

			cursorUpdate.moveToFirst();
			if (!cursorUpdate.isAfterLast()) temp = cursorUpdate.getLong(cursorUpdate.getColumnIndex(COLUMN_ID_DB));

			cursorUpdate.close();
		}
		cursor.close();
		close();
		return temp;
	}

	public void deleteAll()
	{
		open();

		System.out.println("account deleted all");
		// _database.rawQuery("DELETE FROM " + TABLE_NAME, null); not work
		// _database.rawQuery("DELETE FROM " + TABLE_NAME + " WHERE 1", null);
		_database.delete(TABLE_NAME, "1", null);
		close();
	}

	public List<Account> getAll()
	{
		open();

		List<Account> listAccounts = new ArrayList<Account>();

		Cursor cursor = _database.query(TABLE_NAME, _allColumns, null, null, null, null, null);
		cursor.moveToFirst();
		while (!cursor.isAfterLast())
		{
			Account account = cursorToAccount(cursor);
			listAccounts.add(account);
			cursor.moveToNext();
		}
		// Make sure to close the cursor
		cursor.close();
		close();
		return listAccounts;
	}

	private Account cursorToAccount(Cursor cursor)
	{
		Account account = new Account();
		account.setIdDB(cursor.getLong(cursor.getColumnIndex(COLUMN_ID_DB)));
		
		account.setAmount(cursor.getLong(cursor.getColumnIndex(COLUMN_AMOUNT)));
		account.setDateCreated(cursor.getLong(cursor.getColumnIndex(COLUMN_DATE_CREATED)));
		account.setGroupId(cursor.getInt(cursor.getColumnIndex(COLUMN_GROUP_ID)));

		account.setId(cursor.getString(cursor.getColumnIndex(COLUMN_ID)));
		account.setActive(cursor.getString(cursor.getColumnIndex(COLUMN_IS_ACTIVE)).equals("1") ? true : false);
		account.setTerminated(cursor.getString(cursor.getColumnIndex(COLUMN_IS_TERMINATED)).equals("1") ? true : false);
		account.setName(cursor.getString(cursor.getColumnIndex(COLUMN_NAME)));

		account.setPassword(cursor.getString(cursor.getColumnIndex(COLUMN_PASSWORD)));
		account.setPermitAccess(cursor.getInt(cursor.getColumnIndex(COLUMN_PERMIT_ACCESS)));
		account.setPinCode(cursor.getString(cursor.getColumnIndex(COLUMN_PIN_CODE)));
		account.setPositionId(cursor.getInt(cursor.getColumnIndex(COLUMN_POSITION_ID)));
		
		account.setStaffId(cursor.getString(cursor.getColumnIndex(COLUMN_STAFF_ID)));
		account.setUsername(cursor.getString(cursor.getColumnIndex(COLUMN_USER_NAME)));
		
		return account;
	}

	public final boolean isTableExist()
	{
		open();

		boolean isExist = false;
		Cursor cursor = _database.rawQuery("select DISTINCT tbl_name from sqlite_master where tbl_name = '"
				+ TABLE_NAME + "'", null);
		if (cursor != null)
		{
			if (cursor.getCount() > 0) isExist = true;
		}

		cursor.close();
		close();

		return isExist;
	}
}
